<?php
/**
 * Created by JetBrains PhpStorm.
 * User: andrei
 * Date: 7/31/12
 * Time: 12:26 PM
 * To change this template use File | Settings | File Templates.
 */

class Db_Cms_Pages extends Common {

    protected static $tableName = TAB_PAGES;

    protected static $dbFields = array(
        'id',
        'p_name',
        'p_type_id',
        'p_url',
        'p_is_index',
        'p_is_login',
        'p_dynamic_url',
        'p_controller',
        'p_title',
        'p_dynamic_title',
        'p_meta_tags',
        'p_meta_description',
        'p_before_head',
        'p_before_body',
        'p_google_analytics',
        'p_google_analytics_global',
        'p_show_on_sitemap',
        'p_no_index_follow',
        'p_404_error',
        'p_cache_group_id',
		'p_permission_id',
		'p_translation_module',
		'p_translation_key',
		'p_pagination_type',
		'p_pagination_category_id',
		'p_pagination_tag_id',
		'p_items_result_per_page',
        'deleted'
    );
    /**
     * @var Db_Cms_Pages
     */
    public $objectArray;

    public $id;
    public $p_name;
    public $p_type_id;
    public $p_url;
    public $p_is_index;
    public $p_is_login;
    public $p_dynamic_url;
    public $p_controller;
    public $p_title;
    public $p_dynamic_title;
    public $p_meta_tags;
    public $p_meta_description;
    public $p_before_head;
    public $p_before_body;
    public $p_google_analytics;
    public $p_google_analytics_global;
    public $p_show_on_sitemap;
    public $p_no_index_follow;
    public $p_404_error;
    public $p_cache_group_id;
	public $p_permission_id;
	public $p_translation_module;
	public $p_translation_key;
	public $p_pagination_type;
	public $p_pagination_category_id;
	public $p_pagination_tag_id;
	public $p_items_result_per_page;
    public $deleted;

	function __construct($value=NULL, $fieldName=NULL) {
        parent::__construct($value, $fieldName);
    }

    /**
     * @static
     * @return array page info
     */
    public static function getCurrentPage() {

		if(!self::$PAGE) {
			// http://domain/page/ = http://domain/page
			if (defined('CLI')) {
				global $argv;
				$url = $argv[1];
			} else {
				$url = rtrim(get('page'),'/');
			}


			if($url == '') {
				$where = " p_is_index = 1 ";
			} else {
				$url = self::$DB->escapeValue($url);
				$where = " (
								(
									(p_url = {$url} AND pp.pp_pagination_number=1) OR
									(p_url = {$url} AND pp.pp_pagination_number IS NULL)
								) XOR
								IF(pp.pp_page_url <> '', pp.pp_page_url = {$url}, 0)
							) ";
			}

			$sql = "SELECT
						p.*, p.id as page_id, pt.*, mp.*, m.*, m.id as menu_id, pm.pm_identifier, pp.*,pp.pp_pagination_number,
						IF(pp.pp_page_url != '', IF(pp.pp_pagination_number > 1, pp.pp_page_url, p.p_url), p.p_url) AS p_url,
						IF(pp.pp_page_title != '', IF(pp.pp_pagination_number > 1, pp.pp_page_title, p.p_title), p.p_title) AS p_title,
						`p`.`p_url` AS original_url
					FROM ?f p
					INNER JOIN ?f pt ON pt.id = p.p_type_id AND pt.deleted = 0
					INNER JOIN ?f mp ON mp.mp_page_id = p.id AND mp.deleted = 0
					INNER JOIN ?f m ON m.id = mp.mp_menu_id AND m.deleted = 0
					LEFT JOIN ?f pm ON p.p_permission_id = pm.id AND pm.deleted = 0
					LEFT JOIN ?f pp ON pp.pp_page_id = p.id AND pp.deleted = 0
					WHERE {$where} AND p.deleted = 0 AND m.m_project_identifier = ?;";
			self::$PAGE =  self::$DB->selectRow($sql, self::$tableName, TAB_PAGE_TYPES, TAB_MENU_PAGES, TAB_MENUS, TAB_PERMISSIONS, TAB_PAGE_PAGINATION, self::$PROJECT['pr_identifier']);
		}
		return self::$PAGE;
    }

    /**
     * @static
     * @return mixed
     *  return pages are login
     */
    public static function getLoginPage() {
		$sql = "SELECT p.*, p.id as page_id, mp.*, m.*, m.id as menu_id FROM ?f p
				INNER JOIN ?f mp ON mp.mp_page_id = p.id
				INNER JOIN ?f m ON m.id = mp.mp_menu_id AND m.deleted = 0
				WHERE  p_is_login = 1 AND p.deleted = 0 AND m.m_project_identifier = ?;";
		return self::$DB->selectRow($sql, self::$tableName, TAB_MENU_PAGES, TAB_MENUS, self::$PROJECT['pr_identifier']);
    }


    /**
     * @static
     * @param $pageId
     * @return bool|mixed
     */
    public static function getPageInfoById($pageId) {
        if (!$pageId) {
            return false;
        }
        return self::$DB->selectRow("SELECT * FROM ?f WHERE `deleted` = 0 AND `id` = ?d",self::$tableName, $pageId);
    }


	/**
	 * @static
	 * @return mixed
	 */
	public static function getCurrentPageId() {
		self::getCurrentPage();
		return self::$PAGE['page_id'];
	}


    /**
     * @static
     * @param $limit
     * @param string $searchSql
     * @param string $orderBy
     * @return mixed
     */
    public static function loadAllPages($limit, $searchSql = '', $orderBy = '') {
        if (empty($searchSql)) {
            $searchSql = '1=1';
        }
        if (empty($orderBy)) {
            $orderBy = ' ORDER BY `p_name` ASC ';
        }
        $sql = "SELECT `p`.`p_items_result_per_page`, `p`.`p_pagination_type`, `p`.`id` as `page_id`,`p`.`p_name`,`p`.`p_url`,`p`.`p_is_index`,`p`.`p_dynamic_url`,`p`.`p_controller`,`p`.`p_title`,`pt`.`pt_title` FROM ?f `p`
                INNER JOIN ?f `pt` ON `p`.`p_type_id` = `pt`.`id`
                WHERE `p`.`deleted` = 0 AND
                {$searchSql} {$orderBy} {$limit};
        ";
        return self::$DB->select($sql,self::$tableName,TAB_PAGE_TYPES);
    }


    /**
     * @static
     * @param string $sqlCondition
     * @return mixed
     */
    public static function getTotalElements($sqlCondition = '1=1') {
        if (empty($sqlCondition)) {
            $sqlCondition = '1=1';
        }
        return self::$DB->selectCell("SELECT COUNT(*) FROM ?f `p` INNER JOIN ?f `pt` ON `p`.`p_type_id` = `pt`.`id`
                WHERE `p`.`deleted` = 0 AND {$sqlCondition};",self::$tableName,TAB_PAGE_TYPES);
    }

	/**
	 * This methods should not be called manually from anywhere.
	 * It is called automatically from Engine::boot();
	 *
	 * @static
	 * @return array 404 page
	 */
	public static function get404Page() {

		$sql = "SELECT p.*, p.id as page_id, pt.*, mp.*, m.*, m.id as menu_id FROM ?f p
				INNER JOIN ?f pt ON pt.id = p.p_type_id AND pt.deleted = 0
				INNER JOIN ?f mp ON p.id = mp.mp_page_id
				INNER JOIN ?f m ON mp.mp_menu_id = m.id AND m.deleted = 0
				WHERE p_404_error = 1 AND p.deleted = 0 AND m.m_project_identifier = ?;";

		return (self::$PAGE = self::$DB->selectRow($sql, self::$tableName, TAB_PAGE_TYPES, TAB_MENU_PAGES, TAB_MENUS, self::$PROJECT['pr_identifier']));

	}

    public static function getAll() {
        return self::$DB->select("SELECT * FROM ?f WHERE deleted = 0 AND p_404_error = 0", self::$tableName);
    }


	/**
	 * Will delete a certain deleted page's related data. Like pagination, bullets, menu pages etc
	 * @static
	 * @param int $id The id of the page
	 * @return bool Will return true if ALL cleanup operations were successful, FALSE otherwise
	 * This function delete all Children From Page By PageID
	 */
	public static function deleteAllChildrenPage($id = 0) {
		if (!$id) {
			return false;
		}
		$success = true;
		$sql = "SELECT `id` FROM ?f WHERE `pp_page_id` = ?i AND deleted = 0;";
		$listPagination = self::$DB->select($sql, TAB_PAGE_PAGINATION, $id);
		$pcbPagePaginationId = array();
		if ($listPagination) {
			foreach ($listPagination as $pagination) {
				$pcbPagePaginationId[] = $pagination['id'];
			}
			$success = $success && Db_Cms_PageCategoryBullets::abstractDeleteByField('pcb_page_pagination_id', $pcbPagePaginationId);;
			$success = $success && Db_Cms_PagePagination::abstractDeleteByField('pp_page_id', $id);
		}
		$sql = "SELECT MAX(`mp`.`mp_position`) FROM ?f mp WHERE `mp_parent_id` = 0 AND `mp`.`mp_display` = 1 AND `mp`.`deleted`=0";
		$lastPosition = self::$DB->selectCell($sql, TAB_MENU_PAGES);
		$sql = "UPDATE ?f mp SET `mp`.`mp_parent_id` = 0, `mp`.`mp_position` = `mp`.`mp_position` + ?d WHERE `mp`.`mp_parent_id` = ?";
		self::$DB->q($sql, TAB_MENU_PAGES, $lastPosition, $id);
		$success = $success && Db_Cms_MenuPages::abstractDeleteByField('mp_page_id', $id);
		return $success;
	}

	/**
	 * @static
	 * @param int $pageId
	 * @return array
	 */
	public static function updateProductPageUrls($pageId = 0) {

		$sqlAdd = '';
		if ($pageId != 0 && $pageId != null) {
			$sqlAdd = " AND p.id = " . self::$DB->escapeValue($pageId);
		}

		$sql = "SELECT p.id as page_id,pp.id as product_page_id,pp.pp_pagination_number,pd.id as product_id,pd.p_default_url,pd.p_name,pp.pp_page_title FROM ?f p
				INNER JOIN ?f mp ON p.id = mp.mp_page_id AND mp.deleted = 0
				INNER JOIN ?f m ON m.id = mp.mp_menu_id AND m.deleted = 0
				INNER JOIN ?f pr ON pr.pr_identifier = m.m_project_identifier AND pr.deleted = 0
				LEFT JOIN ?f cp ON pr.id = cp.cp_project_id
				LEFT JOIN ?f prc ON cp.cp_category_id = prc.prc_category_id
				LEFT JOIN ?f pd ON pd.id = prc.prc_product_id AND pd.deleted = 0
				LEFT JOIN ?f pp ON p.id = pp.pp_page_id AND pd.id = pp.pp_pagination_number AND pp.deleted = 0
				WHERE p.p_pagination_type = ? AND p.p_dynamic_url = 1 AND p.deleted = 0 {$sqlAdd} GROUP BY pd.id,p.id

		";

		$result = self::$DB->q($sql, self::$tableName, TAB_MENU_PAGES, TAB_MENUS, TAB_PROJECTS, TAB_CATEGORY_PROJECTS, TAB_PRODUCT_CATEGORIES, TAB_PRODUCTS, TAB_PAGE_PAGINATION, 'product');

		$productsInsert = array();
		$productsIds = array();

		while ($data = $result->fetch_object()) {
			if ($data->product_id && !$data->product_page_id) {
				$productsInsert[$data->page_id][] = $data;
			}
			$productsIds[$data->page_id][] = $data->product_id;
		}

		$sql = "SELECT p.id as page_id,pp.id as product_page_id,pp.pp_pagination_number FROM ?f pp
				INNER JOIN ?f p ON p.id = pp.pp_page_id AND  p.p_pagination_type = ? AND p.p_dynamic_url = 1 AND p.deleted = 0 {$sqlAdd}
				WHERE pp.deleted = 0 GROUP BY pp.id
		";

		$result = self::$DB->q($sql, TAB_PAGE_PAGINATION, self::$tableName, 'product');

		$productsDelete = array();
		while ($data = $result->fetch_object()) {
			if (is_array($productsIds[$data->page_id]) && !in_array($data->pp_pagination_number, $productsIds[$data->page_id])) {
				$productsDelete[] = $data->product_page_id;
			}
			if(!$data->page_id) {
				$productsDelete[] = $data->product_page_id;
			}
		}

		if (count($productsInsert) > 0) {
			$pagePaginationObj = new Db_Cms_PagePagination();
			foreach ($productsInsert as $dataProduct) {
				foreach ($dataProduct as $data) {
					$pagePaginationObj->id = null;
					$pagePaginationObj->pp_page_id = $data->page_id;
					$pagePaginationObj->pp_pagination_number = $data->product_id;
					$pagePaginationObj->pp_page_title = $data->p_name;
					$pagePaginationObj->pp_page_url = $data->p_default_url;
					$pagePaginationObj->save();
				}
			}

		}

		if (count($productsDelete) > 0) {
			$listIds = implode(',', $productsDelete);
			self::$DB->q("DELETE FROM ?f WHERE id IN(".$listIds.")", TAB_PAGE_PAGINATION);
		}
		return array($productsInsert, $productsDelete);

	}

	/**
	 * @static
	 * @param string $url
	 * @param int $pageId
	 * @param string $type
	 * @param array $inProjects
	 * @return bool
	 */
	public static function checkPageUrlUnique($url='', $pageId = 0, $type='page', $inProjects = array()) {

		if (count($inProjects) == 0 || strlen($url) == 0) {
			return false;
		}

		$sql = "SELECT p.id as page_id,pp.id as pp_id,p.p_url as page_url, pp.pp_page_url,pr.pr_name,pr.pr_identifier FROM ?f p
				LEFT JOIN ?f pp ON p.id = pp.pp_page_id AND pp.deleted = 0
				INNER JOIN ?f mp ON p.id = mp.mp_page_id AND mp.deleted = 0
				INNER JOIN ?f m ON m.id = mp.mp_menu_id AND m.deleted = 0
				INNER JOIN ?f pr ON m.m_project_identifier = pr.pr_identifier AND pr.deleted = 0
				WHERE p.deleted = 0
		";
		$result =  self::$DB->q($sql, self::$tableName, TAB_PAGE_PAGINATION, TAB_MENU_PAGES, TAB_MENUS, TAB_PROJECTS);
		$dataReturn = array();
		while($data = $result->fetch_object()) {
			$dataReturn[$data->pr_identifier][] = $data;
		}

		foreach ($inProjects as $project) {
			foreach ($dataReturn[$project] as $urlsProject) {
				$compare = ($type == 'page') ? $urlsProject->page_id : $urlsProject->pp_id;
				if (($url == $urlsProject->page_url || $url == $urlsProject->pp_page_url) && $pageId != $compare) {
					return true;
				}
			}
		}

		//check if url exist in products table
		if (Db_Products_Products::findByField('p_default_url', $url)) {
			return false;
		}

		return false;
	}

	public static function findDuplicateUrls(){
		$sql = "SELECT `pr`.`pr_name`, `p`.`p_dynamic_url`, IF(`p`.`p_dynamic_url`= 0, `p`.`p_url`, `pp`.`pp_page_url`) AS `url`,
					IF(`p`.`p_dynamic_url`= 0, `p`.`id`, `pp`.`id`) AS `distinctids`, GROUP_CONCAT(`p`.`id`) AS `page_ids`,
					GROUP_CONCAT(`pp`.`id`) as `pagination_ids` FROM ?f pr
					INNER JOIN ?f m ON `pr`.`pr_identifier` = `m`.`m_project_identifier` AND `m`.`deleted`=0
					INNER JOIN ?f mp ON `mp`.`mp_menu_id` = `m`.`id`
					INNER JOIN ?f p ON `p`.`id` = `mp`.`mp_page_id`
					INNER JOIN ?f pt ON `p`.`p_type_id` = `pt`.`id` AND NOT `pt`.`pt_title` LIKE 'menu_group'
					LEFT JOIN ?f pp ON `pp`.`deleted` = 0 AND `p`.`p_dynamic_url` = 1 AND `p`.`id` = `pp`.`pp_page_id`
				WHERE `pr`.`deleted` = 0
				GROUP BY `pr`.`id`, `url` HAVING COUNT(DISTINCT `distinctids`) > 1";
		return self::findArray($sql, TAB_PROJECTS, TAB_MENUS, TAB_MENU_PAGES, TAB_PAGES, TAB_PAGE_TYPES, TAB_PAGE_PAGINATION );
	}

	/**
	 * @static
	 * @param string $type
	 * @param int $pageId
	 * @param $prIdentifiers
	 * @return bool|mixed
	 */
	public static function checkPageIsUniqueInProjectByType($type = 'product', $pageId = 0, $prIdentifiers) {

		$prIdentifiersList = implode(',',array_map(array('MySQL', 'escapeValue'), $prIdentifiers));

		$allowedTypes = array('product', 'login', 'index', '404');

		if (!in_array($type,$allowedTypes)) {
			return false;
		}
		$where = '';
		switch ($type) {
			case 'product' : {
				$where = " AND p_pagination_type = '" . $type ."'";
				break;
			}
			case 'login' : {
				$where = " AND p_is_login = 1";
				break;
			}
			case 'index' : {
				$where = " AND p_is_index = 1";
				break;
			}
			case '404' : {
				$where = " AND p_404_error = 1";
			}
		}

		$sqlAdd = '';
		if (intval($pageId) != 0) {
			$sqlAdd = " AND p.id != " . self::$DB->escapeValue($pageId);
		}

		$sql = "SELECT p.id, pr.pr_identifier FROM ?f p
				INNER JOIN ?f mp ON p.id = mp.mp_page_id AND mp.deleted = 0
				INNER JOIN ?f m ON mp.mp_menu_id = m.id AND m.deleted = 0
				INNER JOIN ?f pr ON m.m_project_identifier = pr.pr_identifier AND pr.pr_identifier IN ({$prIdentifiersList})
				WHERE p.deleted = 0 {$where} {$sqlAdd} GROUP BY pr.pr_identifier,p.id
		";

		return self::$DB->select($sql, self::$tableName, TAB_MENU_PAGES, TAB_MENUS, TAB_PROJECTS);

	}

	/**
	 * @static
	 * @param $projectIdentifier
	 * @return array
	 */
	public static function getPagesForSitemap($projectIdentifier) {

		$sql = "SELECT p.id as page_id, p.p_dynamic_url,
				p.p_title,p.p_name,p.p_url,p.p_pagination_type,mp.mp_parent_id, p_pagination_category_id,
				p_items_result_per_page, mp.mp_item_title,mp.mp_item_icon,mp.mp_menu_id,mp.mp_position
				FROM ?f p
				INNER JOIN ?f mp ON p.id = mp.mp_page_id AND mp.deleted = 0
				INNER JOIN ?f m ON mp.mp_menu_id = m.id AND m.deleted = 0
				INNER JOIN ?f pr ON m.m_project_identifier = pr.pr_identifier
				INNER JOIN ?f pt ON `p`.`p_type_id` = `pt`.`id`
				WHERE p.p_show_on_sitemap = 1 AND p.deleted = 0 AND pr.pr_identifier = ? AND pt.pt_show_on_sitemap = 1 ORDER BY mp.mp_position
		";


		$result = self::$DB->q($sql, self::$tableName, TAB_MENU_PAGES, TAB_MENUS, TAB_PROJECTS, TAB_PAGE_TYPES, $projectIdentifier);
		$dataReturn = array();
		while ($data = $result->fetch_assoc()) {
			$dataReturn[$data['page_id']] = $data;
		}

		return $dataReturn;
	}

	/**
	 * Returns an array of page paginations. Works only with category and poems pagination types
	 * @param int $page The page array. The page must not be abstract deleted. If the page does not exist, the function will return false.
	 * @param bool|int $currentPage If supplied, the function will also
	 * fetch all the products from the necessary category and the necessary page, and also the bullets for the paginations,
	 * or, respectively, all the poems for the current project and current page
	 * @return bool|array False on failure and an array of paginations on success.
	 */
	//TODO simplify the function, see PagePagination::getPaginationBullets
	public static function getPaginationUrlsByPage($page, $currentPage=false) {
		if ($page['p_pagination_type'] == 'category') {
			$totalItems = Db_Products_Products::countValidProductsForCategory($page['p_pagination_category_id'], self::$PROJECT['pr_identifier']);
		} else if ($page['p_pagination_type'] == 'tags') {
			$totalItems = Db_Products_Products::countProductsByTagId($page['p_pagination_tag_id'], self::$PROJECT['id']);
		} else {
			$sql = "SELECT COUNT(*) FROM ?f pe WHERE `pe`.`pe_show_on_page` = 1 AND `pe`.`deleted` = 0 AND `pe`.`pe_project_identifier` = ?";
			$totalItems = self::$DB->selectCell($sql, TAB_POEMS, self::$PROJECT['pr_identifier']);
		}

		if ($totalItems) {
			$resultPaginations = array();
			$totalPages = ceil($totalItems/$page['p_items_result_per_page']);
			/* if we're on a category page pagination */
			if ('tags' == $page['p_pagination_type'] || 'category' == $page['p_pagination_type'] && (int)$currentPage > 0) {
				$resultPaginations = Db_Cms_PagePagination::getPaginationWithBullets($page['page_id'], $currentPage, $totalPages);
			} else {
				$sql = "SELECT * FROM ?f p
					INNER JOIN ?f pp ON `p`.`id` = `pp`.`pp_page_id` AND `pp`.`deleted` = 0
					WHERE `p`.`id` = ?d AND `pp`.`pp_pagination_number` <= ?d ORDER BY `pp`.`pp_pagination_number` ASC";
				$raw = self::$DB->q($sql, TAB_PAGES, TAB_PAGE_PAGINATION, $page['page_id'], $totalPages);
				while ($entry = $raw->fetch_assoc()) {
					$resultPaginations[$entry['pp_pagination_number']] = $entry;
				}
			}
			$originalUrl = isset($page['original_url']) ? $page['original_url'] : $page['p_url'];
			for ($pageNr=1; $pageNr<=$totalPages; $pageNr++) {
				if (!isset($resultPaginations[$pageNr])) {
					$resultPaginations[$pageNr] = array(
						'pp_page_url' => $originalUrl . '?p=' . ($pageNr),
						'pp_page_title' => $page['p_title']
					);
				}
			}

			$resultPaginations[1]['pp_page_url'] = $originalUrl;
			$resultPaginations[1]['pp_page_title'] = $page['p_title'];
			/* when we're on a product category page pagination, we must also fetch the products for the current pagination */
			if ('category' == $page['p_pagination_type'] && false !== $currentPage && isset($resultPaginations[$currentPage])) {
				/* this function checks by itself how much products does a category page show per pagination, and composes
				the LIMIT considering this value and the current page value*/
				$itemsPerPage = self::$PAGE['p_items_result_per_page'];
				$productsToSkip = ($currentPage-1) * $itemsPerPage ;
				$sql = "SELECT `prod`.`product_id` AS `product_id`, `prod`.`pp_page_url`, `prod`.`p_name`, `ph`.`ph_path`, `ph`.`ph_name`,
						`prod`.`p_description`, `ph`.`ph_resized`, `s`.`s_height`, `s`.`s_width`, `t`.`id` as `theme_id`,
						`t`.`te_name`, `c`.`c_color_rgb`, `ph`.`ph_name`,  `ph`.`ph_resized` FROM (
						  SELECT `p`.`id` AS `product_id`, `p`.`p_size_id`,`pp`.`pp_page_url`, `p`.`p_name`, `p`.`p_description`
						  FROM ?f m
							INNER JOIN ?f mp ON `m`.`id` = `mp`.`mp_menu_id`
							INNER JOIN ?f pg ON `mp`.`mp_page_id` = `pg`.`id` AND `pg`.`p_dynamic_url` = 1 AND `pg`.`p_pagination_type` = ? AND `pg`.`deleted` = 0
							INNER JOIN ?f pp ON `pp`.`pp_page_id` = `pg`.`id` AND `pp`.`deleted` = 0
							INNER JOIN ?f pc ON `pc`.`prc_category_id` = ?d AND `pp`.`pp_pagination_number` = `pc`.`prc_product_id`
							INNER JOIN ?f p ON `p`.`id` = `pc`.`prc_product_id` AND `p`.`deleted` = 0
							INNER JOIN ?f s ON `p`.`p_size_id` = `s`.`id`
							INNER JOIN ?f ptf ON `ptf`.`pt_product_id` = `p`.`id`
							INNER JOIN ?f t ON `ptf`.`pt_theme_id` = `t`.`id` AND `t`.`deleted` = 0
							INNER JOIN ?f c ON `t`.`te_color_id` = `c`.`id`
							INNER JOIN ?f ph ON `ptf`.`pt_photo_id` = `ph`.`id` AND `ph`.`deleted` = 0
						  WHERE `m`.`m_project_identifier` = ?
						  GROUP BY `p`.`id`
						  LIMIT ?d, ?d
						) prod
						  INNER JOIN ?f s ON `prod`.`p_size_id` = `s`.`id`
						  INNER JOIN ?f ptf ON `ptf`.`pt_product_id` = `prod`.`product_id`
						  INNER JOIN ?f t ON `ptf`.`pt_theme_id` = `t`.`id` AND `t`.`deleted` = 0
						  INNER JOIN ?f c ON `t`.`te_color_id` = `c`.`id`
						  INNER JOIN ?f ph ON `ptf`.`pt_photo_id` = `ph`.`id` AND `ph`.`deleted` = 0
						ORDER BY `pt_is_default` DESC";
				$res = self::$DB->q($sql, TAB_MENUS, TAB_MENU_PAGES, TAB_PAGES,
					'product', TAB_PAGE_PAGINATION, TAB_PRODUCT_CATEGORIES, $page['p_pagination_category_id'], TAB_PRODUCTS,
					TAB_SIZES, TAB_PRODUCT_THEME_PHOTOS, TAB_THEMES, TAB_COLORS, TAB_PHOTOS, self::$PROJECT['pr_identifier'],
					$productsToSkip, $itemsPerPage, TAB_SIZES, TAB_PRODUCT_THEME_PHOTOS, TAB_THEMES, TAB_COLORS, TAB_PHOTOS);

				$resultPaginations[$currentPage]['products'] = false;
				while($entry = $res->fetch_assoc()) {
					$productId = $entry['product_id'];
					if (!isset($resultPaginations[$currentPage]['products'][$productId])) {
						$resultPaginations[$currentPage]['products'][$productId] = $entry;
						$resultPaginations[$currentPage]['products'][$productId]['themes'] = array();
					}
					$resultPaginations[$currentPage]['products'][$productId]['themes'][] = array(
						'id' => $entry['theme_id'],
						'name' => $entry['te_name'],
						'color' => $entry['c_color_rgb'],
						'path' => $entry['ph_path'],
						'full' => $entry['ph_name'],
						'resized' => $entry['ph_resized']
					);
				}
			} else if ('poems' == $page['p_pagination_type'] && false !== $currentPage && isset($resultPaginations[$currentPage])) {
				/* this function checks by itself how much products does a category page show per pagination, and composes
				the LIMIT considering this value and the current page value*/
				$itemsPerPage = self::$PAGE['p_items_result_per_page'];
				$productsToSkip = ($currentPage-1) * $itemsPerPage ;
				$sql = "SELECT * FROM ?f pe WHERE `pe`.`deleted` = 0 AND `pe`.`pe_project_identifier` = ? LIMIT ?d, ?d;";
				$resultPaginations[$currentPage]['poems'] = self::findArray($sql, TAB_POEMS, self::$PROJECT['pr_identifier'], $productsToSkip, $itemsPerPage);
				if(isset($resultPaginations[$currentPage]['pp_photo_id']) && ($photo = Db_Products_Photos::findArrayById($resultPaginations[$currentPage]['pp_photo_id']))) {
					$resultPaginations[$currentPage]['photo'] = $photo;
				}
				if(isset($resultPaginations[$currentPage]['pp_category_id'])) {
					$resultPaginations[$currentPage]['category'] = Db_Products_Categories::getCategoryById($resultPaginations[$currentPage]['pp_category_id']);
				}
			} else if ('tags' == $page['p_pagination_type'] && false !== $currentPage && isset($resultPaginations[$currentPage])) {
				$itemsPerPage = self::$PAGE['p_items_result_per_page'];
				$productsToSkip = ($currentPage-1) * $itemsPerPage ;
				$sql = "SELECT `prod`.`product_id` AS `product_id`, `t`.`id` as 'theme_id', `t`.`te_name`, `prod`.`pp_page_url`, `prod`.`p_name`,
						`ph`.`ph_path`, `ph`.`ph_name`, `prod`.`p_description`, `ph`.`ph_resized`, `s`.`s_height`, `s`.`s_width`,
						`c`.`c_color_rgb`, `ph`.`ph_resized`
						FROM (
						SELECT `pr`.`id` AS `product_id`, `pp`.`pp_page_url`, `pr`.`p_name`, `pr`.`p_size_id`, `pr`.`p_description`
						FROM ?f AS `pr`
							INNER JOIN ?f AS `prt` ON `pr`.`id` = `prt`.`prt_product_id`
							INNER JOIN ?f AS `tg` ON `prt`.`prt_tag_id` = `tg`.`id`
							INNER JOIN ?f AS `prc` ON `pr`.`id` = `prc`.`prc_product_id`
							INNER JOIN ?f AS `cg` ON `prc`.`prc_category_id` = `cg`.`id`
							INNER JOIN ?f AS `cp` ON `cp`.`cp_category_id` = `cg`.`id`
							INNER JOIN ?f AS `prj` ON `prj`.`id` = `cp`.`cp_project_id`
							INNER JOIN ?f AS `s` ON `pr`.`p_size_id` = `s`.`id`
							INNER JOIN ?f AS `ptp` ON `ptp`.`pt_product_id` = `pr`.`id`
							INNER JOIN ?f AS `th` ON `th`.`id` = `ptp`.`pt_theme_id`
							INNER JOIN ?f AS `cl` ON `cl`.`id` = `th`.`te_color_id`
							INNER JOIN ?f AS `ph` ON `ph`.`id` = `ptp`.`pt_photo_id`
							INNER JOIN ?f AS `m` ON `m`.`m_project_identifier` = `prj`.`pr_identifier`
							INNER JOIN ?f AS `mp` ON `mp`.`mp_menu_id` = `m`.`id`
							INNER JOIN ?f AS `pg` ON `pg`.`id` = `mp`.`mp_page_id` AND `pg`.`p_pagination_type` = ?
							INNER JOIN ?f AS `pp` ON `pp`.`pp_page_id` = `pg`.`id` AND `pp`.`pp_pagination_number` = `pr`.`id`
						WHERE `pr`.`deleted` = 0 AND `tg`.`deleted` = 0 AND `cg`.`deleted` = 0 AND `prj`.`deleted` = 0
						AND `s`.`deleted` = 0 AND `th`.`deleted` = 0 AND `cl`.`deleted` = 0 AND `ph`.`deleted` = 0
						AND `m`.`deleted` = 0 AND `mp`.`deleted` = 0 AND `pg`.`deleted` = 0 AND `pp`.`deleted` = 0
						AND `prt`.`prt_tag_id` = ? AND `prj`.`id` = ? GROUP BY `pr`.`id` LIMIT ?d, ?d
						) `prod`
						  INNER JOIN ?f s ON `prod`.`p_size_id` = `s`.`id`
						  INNER JOIN ?f ptf ON `ptf`.`pt_product_id` = `prod`.`product_id`
						  INNER JOIN ?f t ON `ptf`.`pt_theme_id` = `t`.`id` AND `t`.`deleted` = 0
						  INNER JOIN ?f c ON `t`.`te_color_id` = `c`.`id`
						  INNER JOIN ?f ph ON `ptf`.`pt_photo_id` = `ph`.`id` AND `ph`.`deleted` = 0
						ORDER BY `pt_is_default` DESC";
				$productsByTag = self::$DB->q($sql,
					TAB_PRODUCTS, TAB_PRODUCT_TAGS, TAB_TAGS, TAB_PRODUCT_CATEGORIES, TAB_CATEGORIES, TAB_CATEGORY_PROJECTS,
					TAB_PROJECTS, TAB_SIZES, TAB_PRODUCT_THEME_PHOTOS, TAB_THEMES, TAB_COLORS, TAB_PHOTOS, TAB_MENUS,
					TAB_MENU_PAGES, TAB_PAGES, 'product', TAB_PAGE_PAGINATION, $page['p_pagination_tag_id'], self::$PROJECT['id'],
					$productsToSkip, $itemsPerPage, TAB_SIZES, TAB_PRODUCT_THEME_PHOTOS, TAB_THEMES, TAB_COLORS, TAB_PHOTOS);

				$resultPaginations[$currentPage]['products'] = false;
				while($entry = $productsByTag->fetch_assoc()) {
					$productId = $entry['product_id'];
					if (!isset($resultPaginations[$currentPage]['products'][$productId])) {
						$resultPaginations[$currentPage]['products'][$productId] = $entry;
						$resultPaginations[$currentPage]['products'][$productId]['themes'] = array();
					}
					$resultPaginations[$currentPage]['products'][$productId]['themes'][] = array(
						'id' => $entry['theme_id'],
						'name' => $entry['te_name'],
						'color' => $entry['c_color_rgb'],
						'path' => $entry['ph_path'],
						'full' => $entry['ph_name'],
						'resized' => $entry['ph_resized']
					);
				}
			}
			ksort($resultPaginations);
			return $resultPaginations;
		} else {
			return false;
		}
	}
}